package com.pennphoto.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import com.pennphoto.client.RegisterService;
import com.pennphoto.shared.FieldVerifier;
import com.pennphoto.shared.PennPhotoConstants;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;


/**
 * The server side implementation of the RPC service.
 */
@SuppressWarnings("serial")
public class Register extends RemoteServiceServlet implements
		RegisterService {
	
	public Boolean isUniqueEmail(String email) throws IllegalArgumentException, Exception{
		boolean validity=true;
		Class.forName("oracle.jdbc.OracleDriver").newInstance();
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
		   try {
			  
			   Statement stmt = conn.createStatement();	     
			     try {
				       
				       ResultSet rset = stmt.executeQuery("select email from "+PennPhotoConstants.schemaName+"users");
				       try {
				         while (rset.next()){
				        	 if(email.equals(rset.getString(1))){
				        		 validity=false;break;
				        	 }
				           
				         }
				       } 
				       finally {
				          try { rset.close(); } catch (Exception ignore) {}
				       }
				     } 
				     finally {
				       try { stmt.close(); } catch (Exception ignore) {}
				     }  
		
	}finally {
	     try {  } catch (Exception ignore) {}
	   }
		   return validity;
	}

	public Integer greetServer1(String first,String last,Date dob,String gender,String email,String address,String pass,String inst1,String inst2,String usertype,String start1,String start2,String end1,String end2,String major1,String major2,ArrayList<String> list) throws Exception {
		String success="Registration unsuccessful";
		first = escapeHtml(first);
		last = escapeHtml(last);
		email = escapeHtml(email);
		address = escapeHtml(address);
		pass = escapeHtml(pass);
		inst1 = escapeHtml(inst1);
		inst2 = escapeHtml(inst2);
		start1 = escapeHtml(start1);
		start2 = escapeHtml(start2);
		end1 = escapeHtml(end1);
		end2 = escapeHtml(end2);
		major1 = escapeHtml(major1);
		major2 = escapeHtml(major2);
		int maxuserid=-1;
		int valid=1;
		System.out.println("Date"+dob);
		int day = dob.getDate();
		int month=dob.getMonth()+1;
		int year = dob.getYear()+1900;
		String date =""+ month+"/"+day+"/"+year;
		System.out.println("Date"+date);
		Class.forName("oracle.jdbc.OracleDriver").newInstance();
    	String password="";
	    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
	   try {
		
	     Statement stmt1 = conn.createStatement();
	     try {
	       ResultSet rset1 = stmt1.executeQuery("select max(userid) from "+PennPhotoConstants.schemaName+"users");
	       
	       try {
	         while (rset1.next()){
	           
	           maxuserid=rset1.getInt(1)+1;
	            // Print col 1
	         }
	       } 
	       finally {
	          try { rset1.close(); } catch (Exception ignore) {}
	       }
	     } 
	     finally {
	       try { stmt1.close(); } catch (Exception ignore) {}
	     }
	     
	     
	   
	   try {
		     
		     Statement stmt2 = conn.createStatement();
		     try {
		    	 if (isUniqueEmail(email)){
		       ResultSet rset2 = stmt2.executeQuery("insert into "+PennPhotoConstants.schemaName+"users values("+maxuserid+",'"+first+"','"+last+"',"+"to_date('"+date+"','mm/dd/yyyy')"+",'"+gender+"','"+email+"','"+pass+"','"+address+"')");
		       success="Registered";
		    	 }else maxuserid=-1;
		     }catch(Exception e){e.printStackTrace();}
		     finally {
		       try { stmt2.close(); } catch (Exception ignore) { }
		     }
		   } 
		   finally {
		     try {  } catch (Exception ignore) {}
		   }
	     conn.commit();
	     Statement stmt3 = conn.createStatement();
	     try {
	    	 for(String str:list){
	    		 
	    		 ResultSet rset3 = stmt3.executeQuery("insert into "+PennPhotoConstants.schemaName+"interests values("+maxuserid+",'"+str+"')");
	       
	       try {
	         
	       } catch(Exception e){e.printStackTrace();}
	       finally {
	          try { rset3.close(); } catch (Exception ignore) {}
	       }
	    	 }
	     } 
	     finally {
	       try { stmt3.close(); } catch (Exception ignore) {}
	     }
	   
	   } 
	   finally {
	     try { conn.close(); } catch (Exception ignore) {}
	   }
		

		return maxuserid;
	} 

	/**
	 * Escape an html string. Escaping data received from the client helps to
	 * prevent cross-site script vulnerabilities.
	 * 
	 * @param html the html string to escape
	 * @return the escaped string
	 */
	private String escapeHtml(String str) {
		if (str == null) {
			return null;
		}
		return str.replaceAll("&", "&amp;").replaceAll("<", "&lt;")
				.replaceAll(">", "&gt;");
	}
}

